﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using Hangjing.DBUtility;
using Hangjing.Model;

namespace Hangjing.SQLServerDAL
{
    /// <summary>
    /// 帐户记录
    /// </summary>
    public partial class AccountingDetail
    {
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(AccountingDetailInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into AccountingDetail(");
            strSql.Append("UserId,AddMoney,State,PayType,PayDate,PayState,AddDate,Inve1,Inve2");
            strSql.Append(") values (");
            strSql.Append("@UserId,@AddMoney,@State,@PayType,@PayDate,@PayState,@AddDate,@Inve1,@Inve2");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters =
            {
                new SqlParameter("@UserId", SqlDbType.Int,4) ,
                new SqlParameter("@AddMoney", SqlDbType.Decimal) ,
                new SqlParameter("@State", SqlDbType.Int,4) ,
                new SqlParameter("@PayType", SqlDbType.Int,4) ,
                new SqlParameter("@PayDate", SqlDbType.Int) ,
                new SqlParameter("@PayState", SqlDbType.Int,4) ,
                new SqlParameter("@AddDate", SqlDbType.DateTime) ,
                new SqlParameter("@Inve1", SqlDbType.Int,4) ,
                new SqlParameter("@Inve2", SqlDbType.VarChar,256)

            };

            parameters[0].Value = model.UserId;
            parameters[1].Value = model.AddMoney;
            parameters[2].Value = model.State;
            parameters[3].Value = model.PayType;
            parameters[4].Value = model.PayDate;
            parameters[5].Value = model.PayState;
            parameters[6].Value = model.AddDate;
            parameters[7].Value = model.Inve1;
            parameters[8].Value = model.Inve2;
            return HJConvert.ToInt32(SQLHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), parameters));
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(AccountingDetailInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update AccountingDetail set ");

            strSql.Append(" UserId = @UserId , ");
            strSql.Append(" AddMoney = @AddMoney , ");
            strSql.Append(" State = @State , ");
            strSql.Append(" PayType = @PayType , ");
            strSql.Append(" PayDate = @PayDate , ");
            strSql.Append(" PayState = @PayState , ");
            strSql.Append(" AddDate = @AddDate , ");
            strSql.Append(" Inve2 = @Inve2  ");
            strSql.Append(" where DataId=@DataId ");

            SqlParameter[] parameters =
            {
                new SqlParameter("@DataId", SqlDbType.Int,4) ,
                new SqlParameter("@UserId", SqlDbType.Int,4) ,
                new SqlParameter("@AddMoney", SqlDbType.Decimal,5) ,
                new SqlParameter("@State", SqlDbType.Int,4) ,
                new SqlParameter("@PayType", SqlDbType.Int,4) ,
                new SqlParameter("@PayDate", SqlDbType.Int) ,
                new SqlParameter("@PayState", SqlDbType.Int,4) ,
                new SqlParameter("@AddDate", SqlDbType.DateTime) ,
                new SqlParameter("@Inve2", SqlDbType.VarChar,256)

            };

            parameters[0].Value = model.DataId;
            parameters[1].Value = model.UserId;
            parameters[2].Value = model.AddMoney;
            parameters[3].Value = model.State;
            parameters[4].Value = model.PayType;
            parameters[5].Value = model.PayDate;
            parameters[6].Value = model.PayState;
            parameters[7].Value = model.AddDate;
            parameters[8].Value = model.Inve2;
            return SQLHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获取记录所有字段
        /// </summary>
        /// 此代码由杭景科技代码内部生成器自动生成
        /// <param>DataId</param>
        /// <returns>AccountingDetailInfo</returns>
        public AccountingDetailInfo GetModel(int DataId)
        {
            string sql = "select DataId,UserId,AddMoney,State,PayType,PayDate,PayState,AddDate,Inve1,Inve2 from AccountingDetail where  DataId = @DataId";
            SqlParameter parameter = new SqlParameter("@DataId", SqlDbType.Int, 4);
            parameter.Value = DataId;
            AccountingDetailInfo model = null;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(CommandType.Text, sql, parameter))
            {
                if (dr.Read())
                {
                    model = new AccountingDetailInfo();
                    model.DataId = HJConvert.ToInt32(dr["DataId"]);
                    model.UserId = HJConvert.ToInt32(dr["UserId"]);
                    model.AddMoney = HJConvert.ToDecimal(dr["AddMoney"]);
                    model.State = HJConvert.ToInt32(dr["State"]);
                    model.PayType = HJConvert.ToInt32(dr["PayType"]);
                    model.PayDate = HJConvert.ToInt32(dr["PayDate"]);
                    model.PayState = HJConvert.ToInt32(dr["PayState"]);
                    model.AddDate = HJConvert.ToDateTime(dr["AddDate"]);
                    model.Inve1 = HJConvert.ToInt32(dr["Inve1"]);
                    model.Inve2 = HJConvert.ToString(dr["Inve2"]);
                }
            }
            return model;
        }

        /// <summary>
        /// 获得总记录数
        /// </summary>
        /// 此代码由杭景科技代码内部生成器自动生成
        public int GetCount(string strWhere)
        {
            return (int)SQLHelper.ExecuteScalar(CommandType.StoredProcedure, "pagecount", new SqlParameter("@tblName", "AccountingDetail"), new SqlParameter("@strWhere", strWhere));
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="pagesize">页尺寸</param>
        /// <param name="pageindex">页索引</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="orderName">排序字段</param>
        /// <param name="orderType">排序类型，1为降序，0为升序</param>
        /// <returns>图书列表</returns>
        ///此代码由杭景科技代码内部生成器自动生成
        public IList<AccountingDetailInfo> GetList(int pagesize, int pageindex, string strWhere, string orderName, int orderType)
        {
            IList<AccountingDetailInfo> infos = new List<AccountingDetailInfo>();
            SqlParameter[] parameters =
            {
                new SqlParameter("@tblName", SqlDbType.VarChar,255),
                new SqlParameter("@strGetFields", SqlDbType.VarChar,1000),
                new SqlParameter("@primary", SqlDbType.VarChar,255),
                new SqlParameter("@orderName", SqlDbType.VarChar,255),
                new SqlParameter("@PageSize", SqlDbType.Int),
                new SqlParameter("@PageIndex", SqlDbType.Int),
                new SqlParameter("@OrderType", SqlDbType.Bit),
                new SqlParameter("@strWhere", SqlDbType.VarChar,1500)
            };
            parameters[0].Value = "AccountingDetail";
            parameters[1].Value = "*,   (select eadmin.adminname from eadmin where eadmin.id=AccountingDetail.inve1) AdminName,(select cname from City where cid=AccountingDetail.PayState) as DeliverName";
            parameters[2].Value = "DataId";
            parameters[3].Value = orderName;
            parameters[4].Value = pagesize;
            parameters[5].Value = pageindex;
            parameters[6].Value = orderType;
            parameters[7].Value = strWhere;
            using (SqlDataReader dr = SQLHelper.ExecuteReader(CommandType.StoredProcedure, "pageselectpri", parameters))
            {
                while (dr.Read())
                {
                    AccountingDetailInfo info = new AccountingDetailInfo();
                    info.DataId = HJConvert.ToInt32(dr["DataId"]);
                    info.UserId = HJConvert.ToInt32(dr["UserId"]);
                    info.AddMoney = HJConvert.ToDecimal(dr["AddMoney"]);
                    info.State = HJConvert.ToInt32(dr["State"]);
                    info.PayType = HJConvert.ToInt32(dr["PayType"]);
                    info.PayDate = HJConvert.ToInt32(dr["PayDate"]);
                    info.PayState = HJConvert.ToInt32(dr["PayState"]);
                    info.AddDate = HJConvert.ToDateTime(dr["AddDate"]);
                    info.Inve1 = HJConvert.ToInt32(dr["Inve1"]);
                    info.Inve2 = HJConvert.ToString(dr["Inve2"]);
                    info.adminname = HJConvert.ToString(dr["adminname"]);
                    info.delivername = HJConvert.ToString(dr["delivername"]);

                    info.deluser = HJConvert.ToString(dr["deluser"]);
                    info.deltime = HJConvert.ToDateTime(dr["deltime"]);

                    info.systime = HJConvert.ToDateTime(dr["systime"]);
                    
                    info.delflag = HJConvert.ToInt32(dr["delflag"]);
                    if (info.delflag == 1)
                    {
                        info.adminname = info.deluser;
                        info.AddDate = info.deltime;
                    }

                    infos.Add(info);
                }
            }
            return infos;
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <param name="Id"></param>
        /// 此代码由杭景科技代码内部生成器自动生成
        /// <returns></returns>
        public int DelAccountingDetail(int Id)
        {
            StringBuilder str = new StringBuilder();
            str.Append("delete from AccountingDetail where DataId=@DataId");
            SqlParameter[] Para =
            {
                new SqlParameter("@DataId",SqlDbType.Int)
            };
            Para[0].Value = Id;

            return SQLHelper.ExecuteNonQuery(CommandType.Text, str.ToString(), Para);
        }


        /// <summary>
        /// 批量删除记录
        /// </summary>
        /// <param name="IdList"></param>
        /// <returns></returns>
        /// 此代码由杭景科技代码内部生成器自动生成
        public int DelList(string IdList)
        {
            StringBuilder str = new StringBuilder();
            str.Append("delete from AccountingDetail where DataId in ({0})");

            return SQLHelper.ExecuteNonQuery(CommandType.Text, string.Format(str.ToString(), IdList), null);
        }

        /// <summary>
        ///按科目统计
        /// </summary>
        /// <returns></returns>
        public IList<AccountingDetailInfo> GetBills(string where, string groupby)
        {
            if (where == "")
            {
                where = " 1=1 ";
            }

            IList<AccountingDetailInfo> list = new List<AccountingDetailInfo>();
            string strSql = "SELECT SUM(addmoney) as mymoney ," + groupby + " FROM  AccountingDetail WHERE " + where + "  GROUP BY " + groupby + " ORDER BY " + groupby + " ASC ";

            using (SqlDataReader dr = SQLHelper.ExecuteReader(CommandType.Text, strSql, null))
            {
                while (dr.Read())
                {
                    AccountingDetailInfo info = new AccountingDetailInfo();
                    info.UserId = HJConvert.ToInt32(dr[groupby]);
                    info.AddMoney = HJConvert.ToDecimal(dr["mymoney"]);
                    list.Add(info);
                }
            }

            return list;
        }

        /// <summary>
        /// 删除或者恢复
        /// </summary>
        /// <param name="IdList"></param>
        /// <param name="adminname"></param>
        /// <param name="delflag">0表示恢复，1表示删除</param>
        /// <returns></returns>
        public int DelOrRecoverRecord(string IdList, string adminname, int delflag)
        {
            StringBuilder str = new StringBuilder();
            //删除其他
            str.Append("UPDATE dbo.AccountingDetail SET delflag = " + delflag + ",deluser = '" + adminname + "',deltime=getdate()  where dataid in ({0});");

            return SQLHelper.ExecuteNonQuery(CommandType.Text, string.Format(str.ToString(), IdList), null);
        }


        /// <summary>
        /// 物理删除
        /// </summary>
        /// <param name="IdList"></param>
        /// <returns></returns>
        /// 此代码由杭景科技代码内部生成器自动生成
        public int CompletelyDelList(string IdList)
        {
            StringBuilder str = new StringBuilder();

            //删除其他
            str.Append("delete from Deliver where dataid in ({0});");

            return SQLHelper.ExecuteNonQuery(CommandType.Text, string.Format(str.ToString(), IdList), null);
        }


    }
}

